<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Newsletter_mod extends CI_Model {

    private $_dbTableMessages = 'messages';
    private $_dbTableUsers = 'users';
    private $_dbTableStates = 'states';
    private $_dbTableAffiliate = 'affiliate';
    private $_dbTableLogOnline = 'log_online';
    private $_dbTAbleBilling = 'billing';
    private $_dbTableImages = 'images';

    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }

    public function getStates() {
        $query = $this->db->select("id, name")
                ->from($this->_dbTableStates)
                ->get();
        return $query->result();
    }

    public function getAllUsers($filter = NULL) {
        $this->db->select(
                $this->_dbTableUsers . '.id,' .
                $this->_dbTableUsers . '.username,' .
                $this->_dbTableUsers . '.email,' .
                $this->_dbTableUsers . '.registration_date,' .
                $this->_dbTableUsers . '.ip_address,' .
                $this->_dbTableUsers . '.status,' .
                $this->_dbTableUsers . '.gender,' .
                $this->_dbTableUsers . '.look_for,' .
                $this->_dbTableUsers . '.birthdate,' .
                $this->_dbTableUsers . '.state,' .
                $this->_dbTableUsers . '.zip_code,' .
                $this->_dbTableUsers . '.coins,' .
                $this->_dbTableUsers . '.bonus_coins,' .
                $this->_dbTableUsers . '.expenditure,' .
                $this->_dbTableUsers . '.timestamp'
        );
        if (isset($filter['username']) && $filter['username'] != "")
            $this->db->like('username', $filter['username']);
        
        if (isset($filter['id']) && $filter['id'] != "")
            $this->db->like('id', $filter['id']);
        
        if (isset($filter['gender']) && $filter['gender'] != "")
            $this->db->where('gender', $filter['gender']);
        
        if (isset($filter['status']) && $filter['status'] != "")
            $this->db->where('status', $filter['status']);
        
        if (isset($filter['is_fake']) && $filter['is_fake'] != "")
            $this->db->where('is_fake', $filter['is_fake']);
        
        if (isset($filter['zip_code']) && $filter['zip_code'] != "")
            $this->db->like('zip_code', $filter['zip_code']);
        
        if (isset($filter['state']) && $filter['state'] != "")
            $this->db->where('state', $filter['state']);
        
        if (isset($filter['age_from']) && $filter['age_from'] != "")
            $this->db->where('(SELECT DATE_FORMAT( FROM_DAYS( TO_DAYS( NOW( ) ) - TO_DAYS( birthdate) ) ,  "%Y" )+0) >=', $filter['age_from']);
        
        if (isset($filter['age_to']) && $filter['age_to'] != "")
            $this->db->where('(SELECT DATE_FORMAT( FROM_DAYS( TO_DAYS( NOW( ) ) - TO_DAYS( birthdate) ) ,  "%Y" )+0) <=', $filter['age_to']);
        
        if (isset($filter['start_reg_date']) && $filter['start_reg_date'] != "")
            $this->db->where('registration_date >=', $filter['start_reg_date']);
        if (isset($filter['end_reg_date']) && $filter['end_reg_date'] != "")
            $this->db->where('registration_date <=', $filter['end_reg_date']);
        //last_message
        if (isset($filter['last_message']) && $filter['last_message'] != "")
            $this->db->where("id in (SELECT `from` FROM " . $this->_dbTableMessages . " WHERE DATE(".$this->_dbTableMessages.".time) >='" . $filter['last_message'] ."')");
        //online
        if (isset($filter['online']))
            $this->db->where('id in (SELECT `user_id` FROM ' . $this->_dbTableLogOnline . ')');
        //paying_client
        if (isset($filter['paying_client']))
            $this->db->where('id in (SELECT `user_id` FROM ' . $this->_dbTAbleBilling . ')');
        
        if( isset($filter['have_image'])  )
        {
          if($filter['have_image']=="1")
          {
            //$this->db->from($this->_dbTableImages);          
            //$this->db->where( " `$this->_dbTableImages`.`user_id` = `$this->_dbTableUsers`.`id` " );          
            $this->db->join( $this->_dbTableImages ," $this->_dbTableImages.user_id = $this->_dbTableUsers.id ");
          }
          else if($filter['have_image']=="0")
          {
            $this->db->where("{$this->_dbTableUsers}`.id NOT IN ( SELECT user_id FROM {$this->_dbTableImages}) ");
          }
        }    
        

        
        $this->db->from($this->_dbTableUsers);
        $this->db->order_by($this->_dbTableUsers . '.id', 'DESC');
        $this->db->distinct();
        
        $query = $this->db->get();

        //echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() > 0) {
            $users = $query->result();
            $query->free_result();
        } else {
            $users = NULL;
        }

        $result = array();
        $row = new stdClass();
        if (count($users) > 0) {
            foreach ($users as $user) {
                $row = $user;
                $row->age = date("Y") - ceil(substr($row->birthdate, 0, 4));
                // num_affiliate
                $query = $this->db->select(" COUNT(`" . $this->_dbTableAffiliate . "`.`id`) as num_affiliate ")
                        ->from($this->_dbTableAffiliate)
                        ->where('user_id = ' . $user->id)
                        ->where('released', 1)
                        ->get();
                //echo $this->db->last_query();
                $errors = $this->db->_error_message();
                if (!empty($errors)) {
                    throw new Exception("SQL Error: " . $errors);
                } else {
                    
                }

                if ($query->num_rows() > 0) {
                    $affiliate = $query->row();
                    $row->num_affiliate = $affiliate->num_affiliate;
                    $query->free_result();
                } else {
                    //$affiliate = NULL;
                    $row->num_affiliate = 0;
                }

                
                // Last message
                $query = $this->db->select(" time ")
                        ->from($this->_dbTableMessages)
                        ->where('from', $user->id)
                        ->order_by('time', 'desc')
                        ->get();
                //echo $this->db->last_query();
                $errors = $this->db->_error_message();
                if (!empty($errors)) {
                    throw new Exception("SQL Error: " . $errors);
                } else {
                    
                }

                if ($query->num_rows() > 0) {
                    $message = $query->row();
                    $row->last_message_time = $message->time;
                    $query->free_result();
                } else {
                    
                    $row->last_message_time = " - ";
                }


                $result[] = $row;
            }
        }

        return $result;
    }

}

?>